/*

*/
ALTER PROC SP_SAVE_PROJECT_DETAILS 	
	@CUSTOMER_NAME VARCHAR(100), 
	@ENDCUSTOMER_NAME VARCHAR(100),
	@ENDUSER_NAME VARCHAR(100),
	@CARRIER VARCHAR(100),
	@PROJSTATUS VARCHAR(100),
	@SALESPERSON_NAME VARCHAR(100),
	@MKTG_PART_NUMBER VARCHAR(100),
	@CHIPSET_PART_NAME VARCHAR(100),
	@PROJID INT OUT,
	@PRODID INT OUT
 AS
 BEGIN 
 	DECLARE @DB_BILLING_ADDRESS_ID  INT 
 	DECLARE @DB_SHIPPING_ADDRESS_ID INT 
 	DECLARE @DB_CUSTOMER_ID INT
 	DECLARE @CUSTOMER_ID INT
	DECLARE @ENDCUSTOMER_ID INT
	DECLARE @ENDUSER_ID INT
	DECLARE @SALESPERSON_ID INT
	DECLARE @ANONYMOUS_CUSTOMER_NAME VARCHAR(100)
	DECLARE @ANONYMOUS_END_CUSTOMER_NAME VARCHAR(100)
	DECLARE @ANONYMOUS_END_USER_NAME VARCHAR(100)
	DECLARE @ANONYMOUS_SALES_PERSON_NAME VARCHAR(100)
	DECLARE @END_CUSTOMER_TYPE_ID INT
	DECLARE @END_USER_TYPE_ID INT
	DECLARE @ACTIVE_STATUS_ID INT
	DECLARE @PROJNAME VARCHAR(100)

	SET @ANONYMOUS_CUSTOMER_NAME = 'ANONYMOUS CUSTOMER'
	SET @ANONYMOUS_END_CUSTOMER_NAME = 'ANONYMOUS END CUSTOMER'
	SET @ANONYMOUS_END_USER_NAME = 'ANONYMOUS END USER'
	SET @ANONYMOUS_SALES_PERSON_NAME = 'ANONYMOUS SALES PERSON'

	SELECT @END_CUSTOMER_TYPE_ID = C_TYPE_ID FROM CUSTOMER_TYPES WHERE C_TYPE_NAME = 'END CUSTOMER'
	SELECT @END_USER_TYPE_ID = C_TYPE_ID FROM CUSTOMER_TYPES WHERE C_TYPE_NAME = 'END USER'
	SELECT @ACTIVE_STATUS_ID= RELATIONSHIPSTATUS_ID FROM CUSTOMER_RELATIONSHIP_STATUS WHERE NAME = 'Active'

	IF((SELECT COUNT(*) FROM PRODUCT WHERE P_MARKETING_PART_NUMBER = @MKTG_PART_NUMBER) > 1)
	BEGIN
		--CONSIDER PRIMARY PRODUCT ID
		SELECT @PRODID = PRODUCT_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P_MARKETING_PART_NUMBER = @MKTG_PART_NUMBER AND PS_TYPE_NAME LIKE 'FINISHED GOODS' AND IS_PRIMARY_FG = 0
	END
	ELSE
	BEGIN
		SELECT @PRODID = PRODUCT_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P_MARKETING_PART_NUMBER = @MKTG_PART_NUMBER AND PS_TYPE_NAME LIKE 'FINISHED GOODS'
	END

	IF(@PRODID IS NULL)
	BEGIN
		PRINT 'PRODUCT NOT FOUND'
		RETURN
	END

 	-- CUSTOMER
	IF @CUSTOMER_NAME IS NULL OR LTRIM(RTRIM(@CUSTOMER_NAME)) = ''
	BEGIN
		SET @CUSTOMER_NAME = @ANONYMOUS_CUSTOMER_NAME
	END
 	SELECT @CUSTOMER_ID = CUSTOMER_ID FROM CUSTOMER WHERE C_NAME = LTRIM(RTRIM(@CUSTOMER_NAME))
 	IF ( @@ROWCOUNT = 0 )
 	BEGIN
 		INSERT INTO ADDRESS ( ADDRESS_1, ADDRESS_2, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, TIMESTAMP, CONTACT_PERSON, CELL_NUMBER, ADDRESS_3, ADDRESS_4 ) VALUES ( '','','','','','','','','', CURRENT_TIMESTAMP, '','','','' )
 		SET @DB_BILLING_ADDRESS_ID = ( SELECT @@IDENTITY )		
 	
 		INSERT INTO ADDRESS ( ADDRESS_1, ADDRESS_2, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, TIMESTAMP, CONTACT_PERSON, CELL_NUMBER, ADDRESS_3, ADDRESS_4 ) VALUES ( '','','','','','','','','', CURRENT_TIMESTAMP, '','','','' )
 		SET @DB_SHIPPING_ADDRESS_ID = ( SELECT @@IDENTITY )	 	
 
 		INSERT INTO CUSTOMER ( C_NAME, C_DEFAULT_BILLING_ADDRESS_ID, C_DEFAULT_SHIPPING_ADDRESS_ID, C_REGION, C_REP_OFFICE, C_CUSTOM_REQUIREMENTS, C_ECN_CHANGE_NOTIFICATION, C_DISABLED) VALUES ( @CUSTOMER_NAME, @DB_BILLING_ADDRESS_ID, @DB_SHIPPING_ADDRESS_ID, '', '', 1, 1, 1) 
 		SET @CUSTOMER_ID = ( SELECT @@IDENTITY )
 	END
	IF ( ( SELECT COUNT (*) FROM CUSTOMER_TYPE_RELATIONSHIP WHERE CUSTOMER_ID = @CUSTOMER_ID  AND C_TYPE_ID = 1 ) = 0 )		
	BEGIN
		INSERT INTO CUSTOMER_TYPE_RELATIONSHIP ( CUSTOMER_ID, C_TYPE_ID ) VALUES ( @CUSTOMER_ID, 1 ) 
	END		

	--END CUSTOMER
	IF @ENDCUSTOMER_NAME IS NULL OR LTRIM(RTRIM(@ENDCUSTOMER_NAME)) = ''
	BEGIN
		SET @ENDCUSTOMER_NAME = @ANONYMOUS_END_CUSTOMER_NAME
	END
	SELECT @ENDCUSTOMER_ID = CUSTOMER_ID FROM CUSTOMER WHERE C_NAME = LTRIM(RTRIM(@ENDCUSTOMER_NAME))
 	IF ( @@ROWCOUNT = 0 )
 	BEGIN		
 		INSERT INTO ADDRESS ( ADDRESS_1, ADDRESS_2, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, TIMESTAMP, CONTACT_PERSON, CELL_NUMBER, ADDRESS_3, ADDRESS_4 ) VALUES ( '','','','','','','','','', CURRENT_TIMESTAMP, '','','','' )
 		SET @DB_BILLING_ADDRESS_ID = ( SELECT @@IDENTITY )		
 	
 		INSERT INTO ADDRESS ( ADDRESS_1, ADDRESS_2, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, TIMESTAMP, CONTACT_PERSON, CELL_NUMBER, ADDRESS_3, ADDRESS_4 ) VALUES ( '','','','','','','','','', CURRENT_TIMESTAMP, '','','','' )
 		SET @DB_SHIPPING_ADDRESS_ID = ( SELECT @@IDENTITY )	 	
 
 		INSERT INTO CUSTOMER ( C_NAME, C_DEFAULT_BILLING_ADDRESS_ID, C_DEFAULT_SHIPPING_ADDRESS_ID, C_REGION, C_REP_OFFICE, C_CUSTOM_REQUIREMENTS, C_ECN_CHANGE_NOTIFICATION, C_DISABLED) VALUES ( @ENDCUSTOMER_NAME, @DB_BILLING_ADDRESS_ID, @DB_SHIPPING_ADDRESS_ID, '', '', 1, 1, 1) 
 		SET @ENDCUSTOMER_ID = ( SELECT @@IDENTITY )	 
 	END	
	IF ( ( SELECT COUNT (*) FROM CUSTOMER_TYPE_RELATIONSHIP WHERE CUSTOMER_ID = @ENDCUSTOMER_ID  AND C_TYPE_ID = @END_CUSTOMER_TYPE_ID ) = 0 )		
	BEGIN
		INSERT INTO CUSTOMER_TYPE_RELATIONSHIP ( CUSTOMER_ID, C_TYPE_ID ) VALUES ( @ENDCUSTOMER_ID, @END_CUSTOMER_TYPE_ID ) 
	END
	IF ((SELECT COUNT (*) FROM CUSTOMER_ODM_RELATIONSHIP WHERE PARENTCUSTOMER_ID = @CUSTOMER_ID AND ODMCUSTOMER_ID = @ENDCUSTOMER_ID ) = 0)
	BEGIN
		INSERT INTO CUSTOMER_ODM_RELATIONSHIP (PARENTCUSTOMER_ID, ODMCUSTOMER_ID, RELATIONSHIPSTATUS_ID) VALUES (@CUSTOMER_ID, @ENDCUSTOMER_ID, @ACTIVE_STATUS_ID)
	END

	--END USER
	IF @ENDUSER_NAME IS NULL OR LTRIM(RTRIM(@ENDUSER_NAME)) = ''
	BEGIN
		SET @ENDUSER_NAME = @ANONYMOUS_END_USER_NAME
	END
	SELECT @ENDUSER_ID = CUSTOMER_ID FROM CUSTOMER WHERE C_NAME = LTRIM(RTRIM(@ENDUSER_NAME))
 	IF ( @@ROWCOUNT = 0 )
 	BEGIN		
 		INSERT INTO ADDRESS ( ADDRESS_1, ADDRESS_2, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, TIMESTAMP, CONTACT_PERSON, CELL_NUMBER, ADDRESS_3, ADDRESS_4 ) VALUES ( '','','','','','','','','', CURRENT_TIMESTAMP, '','','','' )
 		SET @DB_BILLING_ADDRESS_ID = ( SELECT @@IDENTITY )		
 	
 		INSERT INTO ADDRESS ( ADDRESS_1, ADDRESS_2, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, TIMESTAMP, CONTACT_PERSON, CELL_NUMBER, ADDRESS_3, ADDRESS_4 ) VALUES ( '','','','','','','','','', CURRENT_TIMESTAMP, '','','','' )
 		SET @DB_SHIPPING_ADDRESS_ID = ( SELECT @@IDENTITY )	 	
 
 		INSERT INTO CUSTOMER ( C_NAME, C_DEFAULT_BILLING_ADDRESS_ID, C_DEFAULT_SHIPPING_ADDRESS_ID, C_REGION, C_REP_OFFICE, C_CUSTOM_REQUIREMENTS, C_ECN_CHANGE_NOTIFICATION, C_DISABLED) VALUES ( @ENDUSER_NAME, @DB_BILLING_ADDRESS_ID, @DB_SHIPPING_ADDRESS_ID, '', '', 1, 1, 1) 
 		SET @ENDUSER_ID = ( SELECT @@IDENTITY )	 
 	END	
	IF ( ( SELECT COUNT (*) FROM CUSTOMER_TYPE_RELATIONSHIP WHERE CUSTOMER_ID = @ENDUSER_ID  AND C_TYPE_ID = @END_USER_TYPE_ID ) = 0 )		
	BEGIN
		INSERT INTO CUSTOMER_TYPE_RELATIONSHIP ( CUSTOMER_ID, C_TYPE_ID ) VALUES ( @ENDUSER_ID, @END_USER_TYPE_ID ) 
	END
	IF ((SELECT COUNT (*) FROM CUSTOMER_ENDCUSTOMER_RELATIONSHIP WHERE PARENTCUSTOMER_ID = @CUSTOMER_ID AND ENDCUSTOMER_ID = @ENDUSER_ID ) = 0)
	BEGIN
		INSERT INTO CUSTOMER_ENDCUSTOMER_RELATIONSHIP (PARENTCUSTOMER_ID, ENDCUSTOMER_ID, RELATIONSHIPSTATUS_ID) VALUES (@CUSTOMER_ID, @ENDUSER_ID, @ACTIVE_STATUS_ID)
	END

	--SALES PERSON
	IF @SALESPERSON_NAME IS NULL OR LTRIM(RTRIM(@SALESPERSON_NAME)) = ''
	BEGIN
		SET @SALESPERSON_NAME = @ANONYMOUS_SALES_PERSON_NAME
	END	
	SELECT @SALESPERSON_ID = SALESPERSON_ID FROM SALES_PERSONS WHERE SALESPERSON_NAME = @SALESPERSON_NAME AND DEACTIVATED = 0
	IF( @@ROWCOUNT = 0 )
	BEGIN
		INSERT INTO SALES_PERSONS(SALESPERSON_NAME,DEACTIVATED) VALUES(@SALESPERSON_NAME,0)
		SET @SALESPERSON_ID = ( SELECT @@IDENTITY )	 
	END
		
	SET @PROJNAME = ISNULL(@CHIPSET_PART_NAME, '') + '_' + ISNULL(@CARRIER, '') + '_' + CONVERT(VARCHAR, @CUSTOMER_ID) + '_' + CONVERT(VARCHAR, @ENDCUSTOMER_ID) + '_' + CONVERT(VARCHAR, @ENDUSER_ID)
	SELECT @PROJID = PROJID FROM PROJSETUP WHERE PROJNAME = @PROJNAME
	IF( @@ROWCOUNT = 0 ) --PROJ NOT FOUND
	BEGIN				
		INSERT INTO PROJSETUP(PROJNAME, PROJSTATUS, CUSTID, ODMID, ENDUSERID, SPERSONID) 
				VALUES(@PROJNAME, @PROJSTATUS, @CUSTOMER_ID, @ENDCUSTOMER_ID, @ENDUSER_ID, @SALESPERSON_ID)
		SET @PROJID = ( SELECT @@IDENTITY )				
	END
	ELSE --UPDATE SALESPERSON AS THIS IS NOT INCLUDED IN PRIMARY KEY
	BEGIN
		INSERT INTO PROJSETUPHISTORY(PROJID, PROJNAME, CUSTID, PROJSTATUS, USERLIST1, USERLIST2, USERLIST3, USERDATE1, USERDATE2, USERTXT1, USERTXT2, USERTXT3, USERTXT4, USERTXT5, USERLIST1VAL, USERLIST2VAL, USERLIST3VAL, PROJDISABLED, ODMID, USERLIST4, USERLIST5, USERLIST6, USERDATE3, USERDATE4, USERTXT6, USERTXT7, USERTXT8, USERTXT9, USERTXT10, USERLIST4VAL, USERLIST5VAL, USERLIST6VAL, SPERSONID)
					    ( SELECT PROJID, PROJNAME, CUSTID, PROJSTATUS, USERLIST1, USERLIST2, USERLIST3, USERDATE1, USERDATE2, USERTXT1, USERTXT2, USERTXT3, USERTXT4, USERTXT5, USERLIST1VAL, USERLIST2VAL, USERLIST3VAL, PROJDISABLED, ODMID, USERLIST4, USERLIST5, USERLIST6, USERDATE3, USERDATE4, USERTXT6, USERTXT7, USERTXT8, USERTXT9, USERTXT10, USERLIST4VAL, USERLIST5VAL, USERLIST6VAL, SPERSONID FROM PROJSETUP WHERE PROJID = @PROJID )
		UPDATE PROJSETUP SET SPERSONID = @SALESPERSON_ID, PROJSTATUS = @PROJSTATUS WHERE PROJID = @PROJID				
	END
	
	IF((SELECT COUNT(*) FROM PROJPRODREL WHERE PROJID = @PROJID AND PRODID = @PRODID) = 0)					
	BEGIN
		INSERT INTO PROJPRODREL(PROJID,PRODID) VALUES(@PROJID,@PRODID)				
	END
 END 